const mysql = require('mysql');

//  封装一个query方法
const dbconfig = {
    host: '127.0.0.1',
    user: 'root',
    password: '',
    database: 'cxblog',
    port: '3306',
    // 可以同时写多条sql语句
    multipleStatements: true
}

const pool = mysql.createPool(dbconfig)

function query(sql, params) {
    return new Promise(function(resolve, reject) {
        pool.getConnection(function(err, conn) {
            if (err) reject(err);
            conn.query(sql, params, function(err, rows) {
                if (err) reject(err);
                resolve(rows);
                conn.release();
            })
        })
    })
}

exports.login = function (user) { 
    let sql  = 'select * from user where username= ?and password=?';
    let params = [user.username,user.password]
    return query(sql,params);
 }

// --------------------------------文章管理------------------------
exports.getAllArtical = function(currentPage){
    currentPage= currentPage || 1;
    let start = (currentPage-1)*5;
    let sql='SELECT articalId,articalName,status,(select userName  from user where userId= artical.userId) as userName,(select className  from classify where classId= artical.classId) as className,createTime  from artical limit ?,5;select count(*) as total from artical; '
    return query(sql,start)
}
exports.addArticalPage=function(){
    let sql ='select classId,className from classify';
    return query(sql)
}
exports.addArtical=function(artical){
    let sql ='insert into artical set ?';
    return query(sql,artical)
}
exports.deleteArtical=function(id){
    let sql ='delete from artical where articalId=?';
    return query(sql,id)
}
exports.editArticalPage=function(id){
    let sql ='select *from artical where articalId =?; select classId, className from classify';
    return query(sql,id)
}
exports.editArtical =function(artical){
    let sql='update artical set classId=?,content=?, articalName=? where articalId=?';
    let params =[artical.classId,artical.content,artical.articalName,artical.articalId]
    return query(sql,params)
}
//----------------------------用户管理------------------------
//页面显示所有数据
exports.getAllUser=function(currentPage){
    currentPage=currentPage||1;
    let start=(currentPage-1)*5;

    // let sql='select * from user'
    let sql = 'select * from user limit ?,5 ;select count(*) as total from user';

    return query(sql,start)
}

//新增
exports.addUser=function(user){
    let sql='insert into user set ?'
    return query(sql,user)
}

//编辑
exports.getOneUser=function(id){
    let sql='select * from user where userId=?'
    return query(sql,id)
}
exports.editUser=function(user){
    let sql='update user set username=?,password=?,email=? ,status=? where userId=?'
    let params=[user.username,user.password,user.email,user.status,user.userId]
    return query(sql,params)
}

//删除
exports.deleteUser=function(id){
    let sql = 'delete from user where userId=?'

    return query(sql, id)
}
//搜索
exports.searchUser=function(txt){
    let sql='select * from user where username=?'
    return query(sql,txt)

}

// ----------------菜单管理----------------------
exports.getAllMenu = function() {
    let sql = 'select * from menu ';
    return query(sql);
}
exports.AddMenu = function(menu){

    
    let sql = 'insert into menu set ?';
    return query(sql,menu);
}
exports.getOneMenu =function(menuId){
    let sql = 'select * from menu where menuId = ?';
    return query(sql,menuId);
}
exports.editMenu = function(menu){
    let params = [menu.menuName,menu.status,menu.linkUrl,menu.menuId]
    let sql = 'update menu set menuName=?,status=?,linkUrl =? where menuId = ? ';
    return query(sql,params);
}
exports.deleteMenu = function(menuId){
    
    let sql = 'delete from menu where menuId =? ';
    return query(sql,menuId);
}

// --------------文章分类--------------------
exports.getAllClasses = function() {
    let sql = 'select * from classify ';
    return query(sql);
}
exports.AddClass = function(aClass){

    
    let sql = 'insert into classify set ?';
    return query(sql,aClass);
}
exports.getOneClass =function(classId){
    let sql = 'select * from classify where classId = ?';
    return query(sql,classId);
}
exports.editClass = function(aClass){
    let params = [aClass.className,aClass.status,aClass.classId];
        let sql = 'update classify set className=?,status=?where classId = ? ';
    return query(sql,params);
}
exports.deleteClass= function(classId){
    
    let sql = 'delete from classify where classId =? ';
    return query(sql,classId);



}
